1. HOME
  2. [종료]구루비 DB 스터디
  3. 2012년 하반기 - 오라클 데이터베이스 멘토링 스터디
  4. 1st_오라클 Lock_1

1st_오라클 Lock_1 (by samdasoo) [2012.10.13]

## 오라클 Lock ##



\- Latch : SGA내의 자료구조를 보호할 목적으로 사용, SGA 내부에서만 사용되며, 큐로 관리 되지 않음


\- Lock : Table, Data Block 등의 Object를 보호할 목적으로 사용, 큐로 관리됨

1. Enqueue Lock

: Table, Transaction, Tablespace, Sequence, Temporary Segment 등과 같은 Resource 대한 Access를 관리하는 Lock, 큐로 관리됨

(1) Enqueue Resource
  • Owner(소유자), Waiter(대기자), Structure(목록을 관리할 수 있는 구조체)
  • Enqueue Lock으로 관리되는 Resource에 대한 Lock을 획득하려면 "Enqueue Resource"를 할당 받아야 됨
  • Enqueue 리소스는 <Type-ID1-ID2>와 같은 식별자로 구성됨

=> Type
TX : Transaction에 의한 DML Row Lock
TM : DML Table Lock
TS : Temp Access Lock

=> ID1, ID2
Lock 종류별 정보


SQL> select type, id1_tag,id2_tag,name, description from v$lock_type where type in ('TM','TX','TS');

TYPE       ID1_TAG         ID2_TAG                   NAME                 DESCRIPTION
---------- --------------- ------------------------- -------------------- --------------------------------------------------
TM         object #        table/partition           DML                  Synchronizes accesses to an object
TS         tablespace ID   dba                       Temporary Segment    Serializes accesses to temp segments
TX         usn<<16 | slot  sequence                  Transaction          Lock held by a transaction to allow other transact
                                                                          ions to wait for it





- v$lock_type : Lock의 유형별 설명 (10g 이상) -

ex)
SQL> select type, name, description from v$lock_type;

TYPE       NAME                                               DESCRIPTION
---------- -------------------------------------------------- --------------------------------------------------
KM         Scheduler                                          Synchronizes various Resource Manager operations
KT         Scheduler Plan                                     Synchronizes accesses to the current Resource Mana
                                                              ger plan

CI         Cross-Instance Call Invocation                     Coordinates cross-instance function invocations
PR         Process Startup                                    Synchronizes process startup
AK         GES Deadlock Test                                  Lock used for internal testing
DI         GES Internal                                       Coordinates Global Enqueue Service deadlock detect
                                                              ion

RM         GES Resource Remastering                           Coordinates Global Enqueue Service resource remast
                                                              ering

PE         Parameter                                          Synchronizes system parameter updates
PG         Global Parameter                                   Synchronizes global system parameter updates
FP         File Object                                        Synchronizes various File Object(FOB) operations
(2) 리소스 테이블



\- 리소스 테이블을 이용하여 Engueue 리소스 구조체를 관리함



\- 해싱 알고리즘을 사용하여 필요한 Resource를 찾음



\- 각 해시 버킷에는 리소스 구조체가 연결(linked list)되어 있음

  • 해싱을 위한 해시 키는 리소스 식별자





    # Enqueue Lock 작동 메커니즘

1. A 세션이 Shared 모드로 Lock 획득한다.
2. B 세션이 Shared 모드로 Lock을 획득하려고 한다. 먼저 Lock을 소유한 A 세션과 호환되므로 정상적으로 Lock을 획득한다. 이제 소유자 목록에는 두 개 세션이 달려 있다.
3. C 세션이 Exclusive 모드로 Lock을 획득하려고 한다. Shared 모드와 Exclusive 모드 간에 호환성이 없으므로 대기자 목록에 자신을 등록하고 대기한다.
4. 소유자 목록에 Shared 모드로 달려 있던 A, B 두 세션이 모두 Lock을 해제하면 C 세션이 Exclusive 모드로 소유자 목록에 등록된다.
5. A 세션이 Exclusive 모드로 다시 Lock을 획득하려고 하면, Exclusive 모드와 호환되지 않으므로 대기자 목록에 자신을 등록하고 대기한다.
6. B 세션이 다시 Shared 모드로 Lock을 획득하려고 할 때도 Exclusive 모드와 호환되지 않으므로 대기자 목록에 자신을 등록하고 대기한다.
7. Enqueue Lock은 순서가 보장되므로 C 세션이 Lock을 해제하면 A 세션이 가장 먼저 Exclusive 모드로 Lock을 획득한다.

2. TX LOCK(=Transaction Lock)



\- Transaction을 시작하려면 Undo Segment Header에 위치한 Transaction Table로 부터 Slot을 하나 할당 받아야 함


\- DML중 Seleclt 요청이 들어오면 CR블럭을 생성하여 읽음


\- DML중 DML 요청이 들어오면 TX Lock 매터니즘을 사용하여 Access 를 직렬화함

(1) TX Lock 매커니즘



\- TX Lock은 Transaction이 첫번째 변경할 때 얻고, 커밋 또는 롤백시 해제됨





1. TX1 트랜젝션은 Undo 세그먼트에서 트랜젝션 슬롯을 할당받고, Enqueue 리소스를 통해 TX Lock을 설정

  • 이 상태에서 r1부터 r5까지 5개 레코드를 변경하고, 아직 커밋은 하지 않음
    2. TX2 트랜젝션도 트랜젝션 테이블에서 하나의 슬롯을 할당받고, Enqueue 리소스를 통해 TX Lock을 설정
  • r6,r3을 순차적으로 변경하려함,r6 레코드를 변경
    3. TX2가 r3 레코드를 액세스하려는 순간 호환되지 않는 모드로 Lock이 걸려 있음을 인지하고 TX1의 트랜젝션 슬롯 상태를 확인
  • TX1이 아직 커밋되지 않은 Active 상태
  • TX2는 TX1이 Lock을 설정한 Enqueue 리소스 구조체 대기자 목록에 자신을 등록하고 대기상태로 들어감
  • TX2는 대기하면서 3초마다 한번씩 TX1이 설정한 TX Lock의 상태를 확인(교착상태(Deadlock) 발생 여부를 확인하기 위함)
    4. TX1이 커밋 또는 롤백하면 대기자 목록에 있는 TX2는 소유자가 되면서 r3 레코드를 변경

(2) TX Lock 모니터링



- TX Lock 경합 상황 모니터링 -

SQL> select sid, type, id1, id2, lmode, request, block
  2       , to_char(trunc(id1/power(2,16))) USN
  3       , bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
  4       , id2 SQN
  5    from v$lock
  6   where TYPE = 'TX' ;

  SID TY      ID1   ID2   LMODE   REQUEST   BLOCK USN    SLOT     SQN
----- -- -------- ----- ------- --------- ------- --- ------- -------
  145 TX   655401  1601       0         6       0 10       41    1601
  150 TX   655401  1601       6         0       1 10       41    1601
  • 150번 세션이 Exclusive 모드로 Tx Lock을 설정한 후 작업중
  • 145번 세션이 같은 레코드에 Exclusive 모드로 TX Lock 요청하며 대기중






SQL> select sid, seq#, event, state, seconds_in_wait,
  2  decode(to_char(bitand(p1, 65536)), 0, 'None', 1, 'Null', 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX', 6, 'X') "Lock_mode",
  3  trunc(p2/power(2,16)) "USN",
     bitand(p2, to_number('ffff', 'xxxx')) + 0 "SLOT",
  4  p3 "tran_slot_seq"
  5  from v$session_wait
  6  where event like 'enq: TX%' ;


       SID       SEQ# EVENT                               STATE      SECONDS_IN_WAIT Lock_mode         USN       SLOT tran_slot_seq
---------- ---------- ----------------------------------- ---------- --------------- --------------- ----- ---------- -------------
      1087        109 enq: TX - row lock contention       WAITING               5459 None               13         22            41

3. TX Lock > 무결성 제약 위배 가능성 또는 비트맵 인덱스 엔트리 갱신

  • Row Lock은 경합 : Update, Delete 시에만 발생
  • Insert는 Row Lock 경합이 발생하지 않는다.(단, Unique 인덱스일 경우 Insert시에 Row Lock 발생 => 중복 값 체크 때문)

(1) PK Index 설정된 Table에 Insert



SQL> select * from tt01;

        NO NAME
---------- --------------------
         1 aaa
         2 bbb
         3 ccc
         4 ddd
         5 eee
         6 fff
         7 ggg
         8 hhh
         9 iii
        10 jjj

SQL> insert into tt01 values (12,'lll');  --> TX1

1 row created.

SQL> insert into ksy1.tt01 values(12,'lll'); -->TX2



SQL> select sid, type, id1, id2, lmode, request, block
  2       , to_char(trunc(id1/power(2,16))) USN
  3       , bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
  4       , id2 SQN
  5    from v$lock
  6   where TYPE = 'TX' ;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK USN                                      SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---- ----------
      1086 TX     589829        227          0          4          0 9                                           5        227
      1086 TX     851984         41          6          0          2 13                                         16         41


SQL> select sid, seq#, event, state, seconds_in_wait,
  2   decode(to_char(bitand(p1, 65536)), 0, 'None', 1, 'Null', 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX', 6, 'X') "Lock_mode",
  3   trunc(p2/power(2,16)) "USN",
  4   bitand(p2, to_number('ffff', 'xxxx')) + 0 "SLOT",
  5   p3 "tran_slot_seq"
  6   from v$session_wait
  7   where event like 'enq: TX%';

       SID       SEQ# EVENT                                    STATE           SECONDS_IN_WAIT Lock        USN SLOT tran_slot_seq
---------- ---------- ---------------------------------------- --------------- --------------- ---- ---------- ---- -------------
      1086         69 enq: TX - row lock contention            WAITING                      72 None          9    5           227


SQL> commit;  --> TX1 Commit


SQL> insert into ksy1.tt01 values(12,'lll');   --> TX2에서 Error 발생
insert into ksy1.tt01 values(12,'lll')
*
ERROR at line 1:
ORA-00001: unique constraint (KSY1.PK_TT01) violated
(2)PK-FK 설정된 Table에 delete 및 Insert



SQL> select * from tt01; --> no 컬럼에 PK 설정

        NO NAME
---------- ---------------
         1 aaa
         2 bbb
         3 ccc
         4 ddd
         5 eee
         6 fff
         7 ggg
         8 hhh
         9 iii
        10 jjj
        11 kkk
        12 lll

12 rows selected.

SQL> select * from tt02;  --> no 컬럼에 FK 설정

        NO TEL
---------- ----------
         1 !!!
         2 @@@
         3 ###
         4 $$$
         5 %%%
         6 ^^^
         7 &&&
         8 ***
         9 (((
        10 )))
        11 !@#

11 rows selected.

SQL> delete from tt01 where no=12;  -->TX1

1 row deleted.

SQL> insert into ksy1.tt02 values (12,'@#$');  -->TX2



SQL> select sid, type, id1, id2, lmode, request, block
  2       , to_char(trunc(id1/power(2,16))) USN
  3       , bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
  4       , id2 SQN
  5    from v$lock
  6   where TYPE = 'TX' ;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK USN                                            SLOT        SQN
---------- -- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- ----------
      1069 TX      65573        241          0          4          0 1                                                37        241
      1105 TX      65573        241          6          0          1 1                                                37        241

SQL> select sid, seq#, event, state, seconds_in_wait,
  2   decode(to_char(bitand(p1, 65536)), 0, 'None', 1, 'Null', 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX', 6, 'X') "Lock_mode",
  3   trunc(p2/power(2,16)) "USN",
  4   bitand(p2, to_number('ffff', 'xxxx')) + 0 "SLOT",
  5   p3 "tran_slot_seq"
  6   from v$session_wait
  7   where event like 'enq: TX%';

       SID       SEQ# EVENT                               STATE           SECONDS_IN_WAIT Lock        USN       SLOT tran_slot_seq
---------- ---------- ----------------------------------- --------------- --------------- ---- ---------- ---------- -------------
      1069        323 enq: TX - row lock contention       WAITING                      75 None          1         37           241


SQL> commit;  --> TX1 Commit

SQL> insert into ksy1.tt02 values (12,'@#$');  --> TX2에서 Error 발생
insert into ksy1.tt02 values (12,'@#$')
*
ERROR at line 1:
ORA-02291: integrity constraint (KSY1.TT02_FK) violated - parent key not found
(3)비트맵 인덱스에 DML

1. Shared 모드로 enq: TX - row lock contention 대기 이벤트가 발생할 수 있음
2. 비트맵 인덱스의 구조상 하나의 엔트리가 여러 개 레코드와 매핑되고, 하나의 엔트리에 Lock을 설정하면 매핑되는 레코드 전체에 Lock이 설정됨
3. 비트맵 인덱스 엔트리를 두 개 이상 트랜젝션이 동시에 갱신할 때 이 이벤트가 자주 발생
4. TX1 트랜젝션이 1번 레코드를 갱신하는 동안 TX2 트랜젝션이 2번 레코드를 갱신하려고 할 때 Shared 모드로 enq: TX - row lock contention 대기 이벤트가 발생



4. TX Lock > ITL 슬롯 부족

  • 블록의 레코드에 추가/갱신/삭제 시 할당 받을 ITL 슬롯이 없는 경우 Shared 모드로 enq : TX-allocate ITL entry 발생
  • ITL 슬롯은 24바이트의 공간을 차지함
  • ITL 슬롯 개수는 INITRANS로 설정되며, 최대 할당개수는 MAXTRANS에 결정됨
  • ITL 슬롯이 모두 사용중이고 ITL 슬롯을 할당 받기 위해 요청하면 PCTFREE의 공간을 사용
  • 테이블에 insert 할 때는 ITL 슬롯이 부족한 경우 새 블록을 할당해 그곳에 insert 하면 되기 때문에 대기할 필요가 없음(9i이상)
  • 테이블 insert에서는 경합이 발생하지 않지만, index 값 삽입할 때는 ITL 경합이 발생
  • update, delete일 때는 테이블, 인덱스를 불문하고 ITL 경합이 나타날 수 있음


- Table의 INITRANS 재 설정 -
 ** 대상 Table에 Index가 있을 경우 모두 Unusable됨 **

SQL> select TABLE_NAME, PCT_FREE, INI_TRANS, MAX_TRANS from dba_tables where table_name='TT01';

TABLE_NAME                       PCT_FREE  INI_TRANS  MAX_TRANS
------------------------------ ---------- ---------- ----------
TT01                                   10          1        255


SQL> alter table tt01 move initrans 10;

Table altered.


SQL> select TABLE_NAME, PCT_FREE, INI_TRANS, MAX_TRANS from dba_tables where table_name='TT01';

TABLE_NAME                       PCT_FREE  INI_TRANS  MAX_TRANS
------------------------------ ---------- ---------- ----------
TT01                                   10         10        255





- Index의 INITRANS 재 설정 -

SQL> select TABLE_NAME, INDEX_NAME , PCT_FREE, INI_TRANS, MAX_TRANS from dba_indexes where table_name='TT01';

TABLE_NAME                     INDEX_NAME                       PCT_FREE  INI_TRANS  MAX_TRANS
------------------------------ ------------------------------ ---------- ---------- ----------
TT01                           SYS_C003144                            10          2        255

SQL> alter index ksy1.SYS_C003144 rebuild INITRANS 10;

Index altered.

SQL> select TABLE_NAME, INDEX_NAME , PCT_FREE, INI_TRANS, MAX_TRANS from dba_indexes where table_name='TT01';

TABLE_NAME                     INDEX_NAME                       PCT_FREE  INI_TRANS  MAX_TRANS
------------------------------ ------------------------------ ---------- ---------- ----------
TT01                           SYS_C003144                            10         10        255


5. TX Lock > 인덱스 분할

  • 인덱스는 테이블과 다르게 레코드간 정렬상태를 유지하며 Insert시 빈 공간이 없으면 인덱스 분할(split)를 실시해 공간 확보를 함
  • 인덱스 분할(split)시 Lock 경합이 발생될 수 있음
  • 인덱스 분할이 진행되는 동안 같은 블록에 새로운 값을 입력 하려는 트랜잭션은 분할이 끝날때까지 대기해야 하며,
    Shared 모드에서 enq: TX - index contention 이벤트가 밸생됨

    Note 인덱스 분할(TX1)을 진행한 트랙잭션이 커밋을 않하고 다른 작업(TX2)을 하면 TX Lock을 대기 하던 다른 트랙잭션(TX3)은 계속 대기????
    \- 해결 방안 : autonomous_트랜젝션 이용
    인덱스 분할을 진행하는 트랜잭션(TX1)을 autonomous_트랜잭션 으로 구현함


    Note
    # 인덱스 분할의 최소화 방안
  • PCTFREE를 증가시킴 (일시적)



\- 테이블에서의 PCTFREE는 나중에 발생할 UPDATE를 위한 공간


\- 인덱스에서의 PCTFREE는 Insert를 위한 공간
=> 인덱스 분할의 최소화 방안 : PCTFREE를 증가시키면 되나 일시적이므로, 인덱스를 주기적으로 재생성 하는것이 근본적인 해결책임




6. TX Lock > DML 로우 Lock

  • 로우 Lock은 두 개의 동시 트랙잭션이 같은 로우를 변경하는것을 방지함(로우 Lock을 먼저 획득한 트랙잭션이 우선 변경함)
  • 로우 Lock = 로우 Lock + TX Lock
    1. 로우를 갱신하려면 Undo 세그먼트에서 트랜젝션 슬롯을 할당받고
    2. Enqueue 리소스를 통해 TX Lock을 획득(트랜젝션을 시작할 때 한 번만 획득)
    3. insert, update, delete, merge 문장을 통해 갱신하는 각 로우마다 Exclusive 모드로 로우 단위 Lock을 획득

(1) 로우 단위 Lock
  • TX1이 로우를 변경할 때 블록 헤더 ITL 슬롯에 트랜젝션 ID를 기록
  • 로우 헤더에 이를 가리키는 Lock Byte를 설정
    => 로우 헤더에는 현재 갱신중인 트랙잭션의 ITL 슬롯 번호가 저장됨
  • Lock Byte로 \-> ITL 슬롯 찾고 \-> ITL 슬롯에서 Undo 세그먼트 헤더에서 트랜잭션 정보 확인후 액세스 가능 여부 확인
  • 로우 단위 Lock과 다중 버전 읽기 일관성 메커니즘을 이용함으로써 읽기 작업(select for update 문이 아닌)에 대해 Lock에 의한 대기 현상이 발생하지 않도록 구현
(2) TX Lock
  • Enqueue 리소스를 통해 TX Lock을 설정
  • Lock이 설정된 레코드를 갱신하고자 할 때 Enqueue 리소스에서 대기
  • TX1이 갱신중인 레코드를 같이 갱신하려는 TX2 트랜젝션은 TX1 트랜젝션이 완료될 때까지 대기 이를 위해 TX Lock이 필요
  1. 블로킹된 세션은 Exclusive 모드의 enq: TX - row lock contention 대기 이벤트가 지속적으로 나타남 #





7. TM Lock > DML 테이블 Lock

  • 테이블 Lock = TM Lock
  • 로우 Lock 획득시 해당 테이블에 대한 테이블 Lock도 동시에 획득
  • DDL, DML시 테이블 Lock을 이용해 동시성을 제어할때 발생
  • 로우 Lock은 항상 Exclusive 모드이지만, 테이블 Lock에는 여러 가지 Lock 모드가 있음
  • TM Lock의 대기 이벤트로 enq: TM - contention 이벤트가 있음.

(1) Lock 모드간 호환성



- RS(select for update), RX(insert,update,delete,merge) 간에 테이블 Lock 경합은 없으나, 로우 갱신때는 로우 Lock이 발생함

(2) 테이블 Lock(=TM Lock)의 오해
  • 테이블 Lock은 테이블 전체에 Lock 거는것이 아닌, Lock을 획득한 트랜잭션이 해당 테이블에 어떤 작업을 하고 있는지를 알려주는 푯말
  • 후행 트랜잭션은 선행 트랜잭션의 푯말을 보고 호환 모드에 따라 작업 진행 여부가 결정됨
  • 사용자의 명령어 수행으로 후행 트랜잭션의 진로를 결정할 수 있음

\* \* {*}select * from t for update \--> Lock 해제 될때 까지 기다림{*}

  • select * from t for update wait 3 \--> 일정시간 기다리다 포기함{*}
  • select * from t for update nowait \--> 기다림 없이 바로 포기함{*}

{*}lock table emp in exclusive mode NOWAIT; \--> lock table 명령어를 이용 해당 테이블에 옵션을 설정{*}

8. Lock을 푸는 열쇠, 커밋

(1) 블로킹(Blocking)
  • Lock 경합이 발생해 특정 세션의 작업을 진행하지 못하고 멈춰 선 경우
  • 해결방안 : 커밋 또는 롤백
(2) 교착상태(Deadlock)
  • 두 세션이 각각 Lock을 설정한 리소스를 서로 액세스하려고 마주보고 진행하는 상황
  • 둘 중 하나가 물러나지 않으면 영영 풀릴 수 없음
  • ORA-00060: deadlock detected while waiting for resource 에러 발생
  • 교착상태를 발생시킨 문장 하나만 롤백됨
(3) 트랜잭션 구성시 주의사항
  1. 불필요하게 트랜젝션을 길게 정의했을 경우
  • 트랜젝션이 너무 길면 롤백이 필요한 경우 너무 많은 시간이 걸릴 수 있음
  • Undo 세그먼트가 고갈되거나 Undo 세그먼트 경합을 유발할 수도 있음
  • 같은 데이터를 갱신하는 트랜젝션이 동시에 수행되지 않도록 설계
  • DML Lock 때문에 동시성이 저하되지 않도록 적절한 시점에 커밋 처리
  1. 불필요하게 커밋을 너무 자주 수행시
  • Snapshot too old(ORA-01555) 에러를 유발할 가능성 높아짐
  • LGWR가 로그 버퍼를 비우는 동안 발생하는 log file sync 대기 이벤트 때문에 성능 저하 우려
  • 잦은 커밋 때문에 성능이 느리다면 비동기식 커밋 기능 활용 검토(10gR2부터 제공)
(4) 커밋 명령 옵션
  • WAIT(Default)
    LGWR가 로그버퍼를 파일에 기록했다는 완료 메시지를 받을 때까지 대기
    그동안 log file sync 대기 이벤트 발생(동기식 커밋)
  • NOWAIT
    LGWR의 완료 메시지를 기다리지 않고 바로 다음 트랜젝션을 진행
    log file sync 대기 이벤트가 발생하지 않음(비동기식 커밋)
  • IMMEDIATE(Default)
    커밋 명령을 받을 때마다 LGWR가 로그 버퍼를 파일에 기록
  • BATCH
    세션 내부에 트랜젝션 데이터를 일정량 버퍼링했다가 일괄 처리



# commit_write 파라미터를 이용해 시스템 또는 세션 레벨에서 기본 설정을 변경 가능

SQL> show parameter commit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength                integer     1
commit_write                         string
max_commit_propagation_delay         integer     0

SQL> alter session set commit_write=batch,nowait;

Session altered.

SQL>  show parameter commit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength                integer     1
commit_write                         string      BATCH, NOWAIT
max_commit_propagation_delay         integer     0
(5) 각 옵션별 수행속도 테스트

create table t (a number) ;

begin
  for item in 1..100000
  loop
    insert into t values(item);
    commit write [immediate | batch] [wait | nowait];
  end loop;
end;
/

COMMIT WRITE IMMEDIATE WAIT ;   -- 68초
COMMIT WRITE IMMEDIATE NOWAIT ; -- 9초
COMMIT WRITE BATCH WAIT ;       -- 66초
COMMIT WRITE BATCH NOWAIT ;     -- 6초
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2012년 하반기 - 오라클 데이터베이스 멘토링 스터디
  4. 1st_오라클 Lock_1